﻿using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;
using System.Globalization;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace ParserColumnasDescripcionMarca
{
    public class ExcelApp : IDisposable
    {

        static CultureInfo ci = new CultureInfo("es-AR");

        OleDbConnection connection = new OleDbConnection();

        public string ExcelPath { get; private set; }

        public List<CustomExcelSheet> ExcelSheets { get; private set; }

        public CustomExcelSheet SelectedSheet { get; private set; }

        public List<CustomExcelData> ExcelData { get; private set; }


        private Excel.Application _ExcelApp = GetExcelApp();

        public void KillProcess()
        {
            try
            {
                foreach (var item in System.Diagnostics.Process.GetProcesses().Where(x => x.ProcessName.ToUpper().Contains("EXCEL")))
                    item.Kill();
            }
            catch
            {

            }
        }

        private static Excel.Application GetExcelApp()
        {
            return new Excel.Application() { UseSystemSeparators = false, DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator, ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator };
        }

        public void OnProcess(DataGridView _DG, string FileName, bool GeneratePivot)
        {
            _ExcelApp.Application.Workbooks.Add(Type.Missing);
            _ExcelApp.Columns.ColumnWidth = 20;

            var ColCount = _DG.Columns.Count;
            var RowCount = _DG.Rows.Count;

            for (int i = 1; i < ColCount + 1; i++)
                _ExcelApp.Cells[1, i] = _DG.Columns[i - 1].HeaderText;

            for (int i = 0; i < RowCount - 1; i++)
                for (int j = 0; j < ColCount; j++)
                    _ExcelApp.Cells[i + 2, j + 1] = _DG.Rows[i].Cells[j].Value;

            SaveAndFormatExcel(FileName, GeneratePivot);
        }

        private void SaveAndFormatExcel(string FileName, bool GeneratePivot)
        {


            Workbook wb = _ExcelApp.ActiveWorkbook;
            Worksheet sh = wb.ActiveSheet;
            Range range = sh.get_Range("M2", "M65000");
            range.Style = "Currency";
            sh.Columns.AutoFit();

            if (GeneratePivot)
                CreatePivot(wb, sh);

            _ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
            _ExcelApp.ActiveWorkbook.Saved = true;
        }

        private static void CreatePivot(Workbook wb, Worksheet sh)
        {
            Range pivotData = null;
            Range pivotDestination = null;
            string pivotTableName = @"Pivot Table Merge Process";
            PivotTable pivotTable = null;

            PivotField GRUPO_DESC = null;
            PivotField FLIA_DESC = null;
            PivotField COD = null;
            PivotField COMBINADA = null;
            PivotField PRECIO = null;
            PivotField PRES = null;



            // Select a range of data for the Pivot Table.
            pivotData = sh.get_Range("A1", "R65000");

            // Select location of the Pivot Table.
            pivotDestination = sh.get_Range("U1", Type.Missing);

            // Add a Pivot Table to the Worksheet.
            wb.PivotTableWizard(
                XlPivotTableSourceType.xlDatabase,
                pivotData,
                pivotDestination,
                pivotTableName,
                true,
                true,
                true,
                true,
                Type.Missing,
                Type.Missing,
                false,
                false,
                XlOrder.xlDownThenOver,
                0,
                Type.Missing,
                Type.Missing
                );


            // Set variables for used to manipulate the Pivot Table.
            pivotTable = (PivotTable)sh.PivotTables(pivotTableName);

            GRUPO_DESC = ((PivotField)pivotTable.PivotFields(2));
            FLIA_DESC = ((PivotField)pivotTable.PivotFields(4));
            COD = ((PivotField)pivotTable.PivotFields(7));
            PRES = ((PivotField)pivotTable.PivotFields(10));
            PRECIO = ((PivotField)pivotTable.PivotFields(13));
            COMBINADA = ((PivotField)pivotTable.PivotFields(18));


            // Set Sales Region as a Row Field.
            GRUPO_DESC.Orientation = XlPivotFieldOrientation.xlRowField;
            // Set Sales Region as a Row Field.
            FLIA_DESC.Orientation = XlPivotFieldOrientation.xlRowField;
            // Set Sales Region as a Row Field.
            PRES.Orientation = XlPivotFieldOrientation.xlRowField;
            // Set Sales Region as a Row Field.
            COD.Orientation = XlPivotFieldOrientation.xlRowField;
            // Set Sales Region as a Row Field.
            COMBINADA.Orientation = XlPivotFieldOrientation.xlRowField;

            // Set Sum of Sales Amount as a Value Field.
            PRECIO.Orientation = XlPivotFieldOrientation.xlDataField;
            PRECIO.Function = XlConsolidationFunction.xlSum;


            // Format the Pivot Table.
            pivotTable.Format(XlPivotFormatType.xlReport2);
            pivotTable.SmallGrid = false;
            pivotTable.ShowTableStyleRowStripes = true;
            pivotTable.InGridDropZones = false;
        }

        public void OnProcess(System.Data.DataTable _DT, string FileName, bool GeneratePivot)
        {
            _ExcelApp.Application.Workbooks.Add(Type.Missing);
            _ExcelApp.Columns.ColumnWidth = 20;

            var ColCount = _DT.Columns.Count;
            var RowCount = _DT.Rows.Count;

            for (int i = 1; i < ColCount + 1; i++)
                _ExcelApp.Cells[1, i] = _DT.Columns[i - 1].ColumnName;

            for (int i = 0; i < RowCount - 1; i++)
                for (int j = 0; j < ColCount; j++)
                    _ExcelApp.Cells[i + 2, j + 1] = _DT.Rows[i][j].ToString();

            SaveAndFormatExcel(FileName, GeneratePivot);
        }

        public void OnProcess(List<CustomExcelData> _LCED, string FileName)
        {
            //_ExcelApp.Application.Workbooks.Add(Type.Missing);
            //_ExcelApp.Columns.ColumnWidth = 20;

            //var ColCount = CustomExcelData.Columns.Count;
            //var RowCount = _DT.Rows.Count;

            //for (int i = 1; i < ColCount + 1; i++)
            //    _ExcelApp.Cells[1, i] = _DT.Columns[i - 1].ColumnName;

            //for (int i = 0; i < RowCount - 1; i++)
            //    for (int j = 0; j < ColCount; j++)
            //        _ExcelApp.Cells[i + 2, j + 1] = _DT.Rows[i][j].ToString();

            //SaveAndFormatExcel(FileName);
        }

        public void OnProcessAsBinary(DataGridView _DG, string FileName)
        {
            string stOutput = "";
            // Export titles:
            string sHeaders = "";

            for (int j = 0; j < _DG.Columns.Count; j++)
                sHeaders = String.Format("{0}{1}\t", sHeaders, Convert.ToString(_DG.Columns[j].HeaderText));
            stOutput += sHeaders + Environment.NewLine;
            // Export data.
            for (int i = 0; i < _DG.RowCount - 1; i++)
            {
                string stLine = "";
                for (int j = 0; j < _DG.Rows[i].Cells.Count; j++)
                    stLine = String.Format("{0}{1}\t", stLine, Convert.ToString(_DG.Rows[i].Cells[j].Value));
                stOutput += stLine + Environment.NewLine;
            }
            Encoding utf16 = Encoding.GetEncoding(1254);
            byte[] output = utf16.GetBytes(stOutput);
            FileStream fs = new FileStream(FileName, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(output, 0, output.Length); //write the encoded file
            bw.Flush();
            bw.Close();
            fs.Close();
        }





















        public ExcelApp(string _ExcelPath)
        {
            try
            {
                ExcelPath = _ExcelPath;
                ExcelSheets = GetSheets();
            }
            finally { Dispose(); }
        }

        private List<CustomExcelSheet> GetSheets()
        {
            List<CustomExcelSheet> MyList = new List<CustomExcelSheet>();
            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
            connection.Open();
            var Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < Sheets.Rows.Count; i++)
                if (!Sheets.Rows[i].ItemArray[2].ToString().Contains("FilterDatabase"))
                    MyList.Add(new CustomExcelSheet(i, Sheets.Rows[i]));

            return MyList;
        }

        public List<CustomExcelColumn> GetColumns(string SheetName)
        {
            SelectedSheet = ExcelSheets.Where(x => x.Name == SheetName).SingleOrDefault();

            connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
            connection.Open();
            List<CustomExcelColumn> MyList = new List<CustomExcelColumn>();
            string query = string.Format("SELECT * FROM [{0}$]", SheetName);
            OleDbCommand cmd = new OleDbCommand(query, connection);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                MyList.Add(new CustomExcelColumn(i, ds.Tables[0].Columns[i]));

            return MyList;
        }

        public List<CustomExcelData> Process(string ColumnNameA, string ColumnNameB)
        {
            foreach (var item in ExcelData)
                item.COMBINADA = GetDesc(item, ColumnNameA, ColumnNameB);

            return ExcelData;
        }

        public List<string> GetExcelDataByColumn(string ColumnName)
        {
            List<string> MyList = new List<string>();

            var propertyInfo = typeof(CustomExcelData).GetProperty(ColumnName);

            var ColNotTyped = GetExcelData()
                .GroupBy(p => (propertyInfo.GetValue(p, null)))
                .Select(g => g.First())
                .ToList().Select(x => (propertyInfo.GetValue(x, null))).ToList();

            foreach (var item in ColNotTyped)
                MyList.Add(item.ToString());

            return MyList;
        }

        public List<CustomExcelData> GetExcelData()
        {
            List<CustomExcelData> MyList = new List<CustomExcelData>();
            string query = string.Format("SELECT * FROM [{0}$]", SelectedSheet.Name);
            OleDbCommand cmd = new OleDbCommand(query, connection);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            foreach (DataRow item in ds.Tables[0].Rows)
                MyList.Add(new CustomExcelData(item));

            ExcelData = MyList;
            return MyList;
        }

        public List<CustomExcelData> GetExcelData(string ColumnName, string value, bool OnlyProperty)
        {
            var propertyInfo = typeof(CustomExcelData).GetProperty(ColumnName);

            if (OnlyProperty)
                return ExcelData.Where(p => (propertyInfo.GetValue(p, null)).ToString() == value).ToList();
            else
                return ExcelData.Where(p => (propertyInfo.GetValue(p, null)).ToString() != value).ToList();
        }


        private string GetDesc(CustomExcelData item, string ColumnA, string ColumnB)
        {
            var ValueA = item.GetType().GetProperty(ColumnA).GetValue(item, null);
            var ValueB = item.GetType().GetProperty(ColumnB).GetValue(item, null);

            if (string.IsNullOrEmpty(ValueB.ToString()))
                return ValueA.ToString();

            return (ValueB.ToString().Trim() + " " + ValueA.ToString()
                     .Trim()
                     .ToUpper()
                     .Replace(ValueB.ToString().Trim().ToUpper(), ""))
                     .Trim()
                     .Replace('-', ' ')
                     .Replace("                          ", "")
                     .Replace("                         ", "")
                     .Replace("                        ", "")
                     .Replace("                       ", "")
                     .Replace("                      ", "")
                     .Replace("                     ", "")
                     .Replace("                    ", "")
                     .Replace("                   ", "")
                     .Replace("                  ", "")
                     .Replace("                 ", "")
                     .Replace("                ", "")
                     .Replace("               ", "")
                     .Replace("              ", "")
                     .Replace("             ", "")
                     .Replace("            ", "")
                     .Replace("           ", "")
                     .Replace("          ", "")
                     .Replace("         ", "")
                     .Replace("        ", "")
                     .Replace("       ", "");

        }

        public void Dispose()
        {
            connection.Close();
        }
    }
}
